use warnings;
use strict;

#use Win32::ODBC;
use DBI;
use DBD::ODBC;
use Audio::Beep;
use IO::Handle;  # For autoflush
use Carp;

my $g_dir_to_proc = shift;
my $g_new_data_file = $g_dir_to_proc."/new_data.txt";

my %Rules_Replace = ("varchar_[\\d]+_" => "varchar10");

sub parse_line
{
	my $line = shift;
	chomp $line;

	# 1. remove any comments.
	if ($line =~ m%^[\s]*(/\*|\*/|\*)%) {
		return undef
	}

	# 2. change varchar_N_ to varchar(N).
	$line =~ s/varchar_([\d]+)_/varchar\($1\)/ig;

	# 3. change varchar{N} to varchar(N).
	$line =~ s/varchar{([\d]+)}/varchar\($1\)/ig;

	# 4. make primary key identity.
	$line =~ s/primary\s+key/AUTO_INCREMENT $&/i;

	# 5. bracket the table name.
	# $line =~ s/(create[\s]+table[\s]+)([\w]+)/$1\[$2\]/ig;
	
	#6 replace money datatype with decimal(7, 2)
	$line =~ s/(money|smallmoney)/decimal\(7, 2\)/ig;
	
	#7 handle forgein key line for mssql
	$line =~ s/^.*foreign key (\(\w+\))[^,]*/$&$1/i;
	
	#8 replace smalldatetime with TIMNE
	$line =~ s/smalldatetime/TIME/ig;

    return $line;

}
sub process_file
{
  my $input = shift;
  my $output = shift;

  if ($input eq $output) {
	  return 1;
  }
  
  print "Processing data from '$input' into '$output'...\n";
  
  open(INPUT, "<$input") or die $!;
  open(OUTPUT, ">>$output") or die $!;

  while (<INPUT>) {
	  $_ = parse_line($_);
	  
	  if ($_) {
		  print OUTPUT $_."\n";
	  }
  }

  close(OUTPUT);
  close(INPUT);
 
  return 1;
}
sub process_dir
{
  my $dir_to_proc = shift;
  my $output = shift;

  my $dir_handle;

  opendir($dir_handle, $dir_to_proc) or die "Cannot open directory '$dir_to_proc', $!!\n";
  print "Dir $dir_to_proc is open.\n";

  open(OUTPUT, ">>$output");
  print OUTPUT "--##################################DIR $dir_to_proc Start#######################--\n";
  close(OUTPUT);

  while (my $item = readdir($dir_handle)) {
	  $item =~ /(^\.$)|(^\.\.$)/ and next;

	  $item = $dir_to_proc . "/" . $item;
	  if (-f $item) {
		  process_file($item, $output) or closedir($dir_handle) and return undef;
	  }
	  elsif (-d $item) {
		  print "Processing directory '$item'...\n";
		  process_dir($item, $output) or closedir($dir_handle) and return undef;
	  }
	  else{
		  print "Unknown item type:'$item'!!!\n";
		  closedir($dir_handle);
		  return undef;
	  }
  }

  open(OUTPUT, ">>$output");
  print OUTPUT "--##################################DIR $dir_to_proc End#########################--\n";
  close(OUTPUT);

  closedir($dir_handle);
  print "Dir $dir_to_proc is closed.\n";

  return 1;
}

sub test2
{
	my $in = shift;

	print "in test2(), changing in...\n";
	$in = "modified";

	return $in;
}
sub test
{
	my $line = "varchar_100_ jiushi ata.\n";
	print $line;

	my $patt = "varchar_([\\d]+)_";
	my $rep = "varchar$2";
	$line =~ s/($patt)/$rep/ig;
	print $line;
}
sub main
{
  print "Start processing directory '$g_dir_to_proc' into file '$g_new_data_file'...\n";

  unlink($g_new_data_file);
  process_dir($g_dir_to_proc, $g_new_data_file) and print "Successful!\n" or print "Failed!!!\n";

  print "End of processing.\n";
}

main();